package com.market.dao.jpa;

import java.util.List;

import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.market.dao.ProductDao;
import com.market.dao.ProductInfoDao;
import com.market.dao.PurchaseScheduleDao;
import com.market.domain.Picture;
import com.market.domain.Product;
import com.market.domain.ProductInfo;
import com.market.domain.PurchaseSchedule;

@Repository("productDao")
public class ProductDaoJpa extends GenericDaoJpa<Product, Long> implements
		ProductDao {

	@Autowired
	private PurchaseScheduleDao purchaseScheduleDao;
	@Autowired
	private ProductInfoDao productInfoDao;

	public ProductDaoJpa() {
		super(Product.class);
	}

	public void remove(Product object) {
		if (super.findById(object.getId()) == null) {
			log.debug(this.getClass() + " Product with id " + object.getId()
					+ " can not be found, can not be delete ");
		} else {
			super.removeById(object.getId());
			log.debug(this.getClass() + " Product with id " + object.getId()
					+ " is found, will be deleted");
		}
	}

	@Override
	public List<Product> findAll() {
		String sql = "select product.* from product order by number asc";
		Query q = this.em.createNativeQuery(sql, Product.class);
		List<Product> products = q.getResultList();
		return products;
	}

	public List<Product> findAllHaveInventory() {
		String sql = "select product.* from product where inventory >0";
		Query q = this.em.createNativeQuery(sql, Product.class);
		List<Product> products = q.getResultList();
		return products;
	}

	public List<Product> findAllNotFreshHaveInventory() {
		String sql = "select product.* from product where inventory >0 and isFreshFood = 0";
		Query q = this.em.createNativeQuery(sql, Product.class);
		List<Product> products = q.getResultList();
		return products;
	}

	public List<Product> findActiveWeeklyProducts() {
		String sql = "select product.* from  weekly, weekly_product,product where weekly.id= weekly_product.weekly_id and "
				+ " weekly_product.products_id = product.id and weekly.isactive=1";
		Query q = this.em.createNativeQuery(sql, Product.class);
		List<Product> products = q.getResultList();
		return products;
	}

	public List<Picture> findPictures(Product product) {
		String sql = "select picture.* from picture, product_picture where picture.id=product_picture.pictures_id "
				+ "and product_picture.product_id = " + product.getId();
		Query q = this.em.createNativeQuery(sql, Picture.class);
		List<Picture> pictures = q.getResultList();
		return pictures;
	}

	public List<PurchaseSchedule> findPurchaseSchedules(Product product) {
		String sql = "select purchaseschedule.* from purchaseschedule, product_purchaseschedule where "
				+ " purchaseschedule.id=product_purchaseschedule.purchaseschedules_id and product_purchaseschedule.product_id = "
				+ product.getId();
		Query q = this.em.createNativeQuery(sql, PurchaseSchedule.class);
		List<PurchaseSchedule> purchaseSchedule = q.getResultList();
		for (PurchaseSchedule schedule : purchaseSchedule) {
			schedule.setSelected(true);
		}
		return purchaseSchedule;
	}

	public ProductInfo findProductInfo(Product product) {
		String sql = "select productinfo.* from product, productinfo where product.productinfo_id = productinfo.id and  product.id = "
				+ product.getId();
		Query query = this.em.createNativeQuery(sql, ProductInfo.class);
		ProductInfo productInfo = (ProductInfo) query.getSingleResult();
		return productInfo;
	}

	public Integer findInventory(Long productID) {
		String sql = "select inventory from product where id = " + productID;
		Query query = this.em.createNativeQuery(sql);
		Integer inventory = (Integer) query.getSingleResult();
		return inventory;
	}

	public void saveInventory(Product product) {
		String sql = "update product set inventory = " + product.getInventory()
				+ " where id = " + product.getId();
		Query query = this.em.createNativeQuery(sql);
		query.executeUpdate();
	}

	public void savePriceInfo(Product product) {
		String sql = "update product set price = " + product.getPrice()
				+ ", specialprice = " + product.getSpecialPrice()
				+ ", unitprice='" + product.getUnitPrice()
				+ "', chineseunitprice='" + product.getChineseUnitPrice()
				+ "', frenchunitprice='" + product.getFrenchUnitPrice()
				+ "' where id = " + product.getId();
		Query query = this.em.createNativeQuery(sql);
		query.executeUpdate();
	}

	@Override
	public Product save(Product product) {

		// Delete purchaseSchedules that are referenced by this product first,
		// because save product will add new
		// purchaseSchedules, but those purchaseShedules will have new id
		// Because product's purchaseSchedules property have set Cascade to all.
		// String sql =
		// "delete from purchaseschedule where purchaseschedule.id  in "
		// +
		// "(select purchaseschedules_id from product_purchaseschedule where product_id = "
		// + product.getId() + ")";
		//
		// Query query = this.em.createNativeQuery(sql);
		// query.executeUpdate();

		product = super.save(product);

		if (product.getIcon16() != null) {
			try { // Even upload icon fail, I don't want this transation roll
					// back, That's why i use this try block;
				String iconPath = product.getIcon();
				super.uploadFile(iconPath, "16.png", product.getIcon16());
				super.uploadFile(iconPath, "32.png", product.getIcon32());
				super.uploadFile(iconPath, "64.png", product.getIcon64());
				super.uploadFile(iconPath, "128.png", product.getIcon128());
			} catch (Exception e) {
				System.out.println(e);

			}
		}
		return product;
	}
	
	
	

	public List<Product> findAllOrganicProducts() {
		String sql = "select product.* from product where isOrganic = 1";
		Query query = this.em.createNativeQuery(sql, Product.class);
		List<Product> products = query.getResultList();
		return products;
	}

	public List<Product> findAllTofuProducts() {
		String sql = "select product.* from product where product.type = 'TOFU'";
		Query query = this.em.createNativeQuery(sql, Product.class);
		List<Product> products = query.getResultList();
		return products;
	}
}
